/*
 * @(#)GSProviderDAO.java
 * 
 * Project: BUS
 * 
 * Modify Information:
 * =============================================================================
 * Author          Date         Description
 *  ------------ ---------- ------------------------------------- 
 *  Jenny Li      2006-2-11 ����
 *  simonyi peng  2006-3-3    添加方法queryManufactureProvider
 *  Jenny Li      2006-3-7    添加方法queryAllProviderByFlat
 *  Jenny Li      2006-3-20   添加方法queryProvider
 *  Jenny Li      2006-4-13   添加方法queryProviderByName
 *  Jenny Li      2006-5-16   添加方法queryProviderByShortName
 *  Jenny Li      2006-6-1    添加方法queryProviderNameByManuf
 * Copyright Notice:
 * =============================================================================
 * Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room,
 * Science & Technology Building, No. 9 South Street, Zhong Guan Cun, Haidian
 * District, Beijing ,100081, China All rights reserved.
 * 
 * This software is the confidential and proprietary information of Beijing
 * HiTRUST Technology Co., Ltd. ("Confidential Information"). You shall not
 * disclose such Confidential Information and shall use it only in accordance
 * with the terms of the license agreement you entered into with HiTRUST.
 * 
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import system.LoginContext;

import com.hitrust.gs.bean.GSProviderBean;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.lib.db.DBConnectionManager;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSProviderVO;

import framework.core.ConstDefine;
import framework.core.DateUtil;

/**
 * 
 */
public class GSProviderDAO extends GSBaseDAO {
    /**
     * 根据条件查询供应商信息
     * @param aGSProviderBean       供应商数据对象
     * @return
     * @throws GSException
     */
    public ArrayList queryProviderByOpt(GSProviderBean aGSProviderBean) throws Exception {
        ArrayList tList = new ArrayList();
        StringBuffer tSql = new StringBuffer("select * from t_provider where status ='1' and country_code='"
                + aGSProviderBean.getGs_reside_country() + "'");
        if(GSSystemConfigurator.getInstance().getProjectVersionType().equals("SH")&&aGSProviderBean.getGs_validity()!=null&&!aGSProviderBean.getGs_validity().trim().equals("")){
            if(aGSProviderBean.getGs_validity().equals("2")){
                tSql = new StringBuffer("select * from t_provider where  country_code='"
                        + aGSProviderBean.getGs_reside_country() + "'"); 
            }else
               tSql = new StringBuffer("select * from t_provider where status ='"+aGSProviderBean.getGs_validity()+"' and country_code='"
                    + aGSProviderBean.getGs_reside_country() + "'");
        }

        if (aGSProviderBean.getGs_provider_name() != null) {
            tSql.append(" and full_name like '%" + aGSProviderBean.getGs_provider_name() + "%'");
        }
        if (aGSProviderBean.getGs_reside_province().equals("00")) {
            tSql.append(" and province_code like '%'");
        }
        else {
            tSql.append(" and province_code='" + aGSProviderBean.getGs_reside_province() + "'");
        }

        if (aGSProviderBean.getGs_reside_city().equals("0000") || aGSProviderBean.getGs_reside_city().equals(aGSProviderBean.getGs_reside_province())) {
            tSql.append(" and district_code like '%'");
        }
        else {
            tSql.append(" and district_code='" + aGSProviderBean.getGs_reside_city() + "'");
        }
        if (!aGSProviderBean.getGs_contact_id().equals("")) {
            tSql.append(" and link_person like '%" + aGSProviderBean.getGs_contact_id() + "%'");
        }
        if (!aGSProviderBean.getGs_contact_phone().equals("")) {
            tSql.append(" and link_tel like '%" + aGSProviderBean.getGs_contact_phone() + "%'");
        }
        
        
        if(ConstDefine.STR_YES.equals(aGSProviderBean.getIs_audit())) {
        	tSql.append(" and exists (select id from t_provider_type where status='1' and t_provider.id = t_provider_type.provider_id)");
        }
        if(ConstDefine.STR_NO.equals(aGSProviderBean.getIs_audit())) {
        	tSql.append(" and not exists (select id from t_provider_type where status='1' and t_provider.id = t_provider_type.provider_id)");
        }        
          
		tSql.append(" order by code");
        Object[] tObj = {};
        GSLogWriter.debug(this,tSql.toString());
        try {
            tList = this.executeQuery(new GSProviderBean(), tSql.toString(), tObj);
        }
        catch (Exception e) {
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 查询供应商详细信�息
     * @param aProviderId           供应商ID
     * @return                      供应商数据对�象
     * @throws GSException
     */
    public GSProviderBean queryProvider(String aProviderId) throws GSException{
        GSProviderBean tGSProviderBean = null;;
        String tSql = "select * from t_PROVIDER where code=?";
        Object tObj[] = {aProviderId};
        try{
            ArrayList tList = this.executeQuery(new GSProviderBean(),tSql,tObj);
            if(tList.size() > 0){
                tGSProviderBean =(GSProviderBean)tList.get(0);
            }
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tGSProviderBean;
    }
    
    /**
     * 在组织机构信息表GS_PROVIDER中新增一笔记录
     * @author owen
     * @param aGSProviderBean
     * 
     * return boolean
     * 
     * @throws GSException
     */
    public boolean add(GSProviderBean aGSProviderBean) throws GSException {

        String tSql="INSERT INTO t_PROVIDER (CODE,NAME,FULL_NAME," +
                "ENG_NAME,COM_PROPERTY,ADDRESS,ZIP_CODE,MANAGE_PERSON," +
                "MANAGER_TEL,LINK_PERSON,LINK_TEL,COUNTRY_CODE,PROVINCE_CODE," +
                "DISTRICT_CODE,INDUSTRY,MAIN_RPODUCT,SCOPE,CREDIT_LEVEL" +
                ",IS_TRADEFLAT_PROVIDER,STATUS,id,CREATE_USER_ID,CREATE_USER,CREATE_TIME," +
                "LAST_UPDATE_USER_ID,LAST_UPDATE_USER,LAST_UPDATE_TIME) " +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,(select sys_guid() from dual),?,?,?,?,?,?)";

        Object[] tObject = new Object[26] ;
          tObject[0]=aGSProviderBean.getGs_provider_id();
          tObject[1]=aGSProviderBean.getGs_provider_shortname();
          tObject[2]=aGSProviderBean.getGs_provider_name();
          tObject[3]=aGSProviderBean.getGs_provider_engname();
          tObject[4]=aGSProviderBean.getGs_corp_property();
          tObject[5]=aGSProviderBean.getGs_corp_addr();
          tObject[6]=aGSProviderBean.getGs_corp_post();
          tObject[7]=aGSProviderBean.getGs_principal_id();
          tObject[8]=aGSProviderBean.getGs_principal_phone();
          tObject[9]=aGSProviderBean.getGs_contact_id();
          tObject[10]=aGSProviderBean.getGs_contact_phone();
          tObject[11]=aGSProviderBean.getGs_reside_country();
          tObject[12]=aGSProviderBean.getGs_reside_province();
          tObject[13]=aGSProviderBean.getGs_reside_city();
          tObject[14]=aGSProviderBean.getGs_work_trade();
          tObject[15]=aGSProviderBean.getGs_work_manufacture();
          tObject[16]=aGSProviderBean.getGs_work_scope();
          tObject[17]=aGSProviderBean.getGs_credit_level();
          tObject[18]=aGSProviderBean.getGs_is_tradeflat_provider();
          tObject[19]=aGSProviderBean.getGs_validity()==null?"1":aGSProviderBean.getGs_validity();
          tObject[20] = aGSProviderBean.getCreate_user_id();
          tObject[21] = aGSProviderBean.getCreate_user();
          tObject[22] = aGSProviderBean.getCreate_time();
          tObject[23] = aGSProviderBean.getLast_update_user_id();
          tObject[24] = aGSProviderBean.getLast_update_user();
          tObject[25] = aGSProviderBean.getLast_update_time();
          
          
        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);

        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    public boolean add(Connection con, GSProviderBean aGSProviderBean) throws GSException {

        String tSql="INSERT INTO t_PROVIDER (CODE,NAME,FULL_NAME," +
                "ENG_NAME,COM_PROPERTY,ADDRESS,ZIP_CODE,MANAGE_PERSON," +
                "MANAGER_TEL,LINK_PERSON,LINK_TEL,COUNTRY_CODE,PROVINCE_CODE," +
                "DISTRICT_CODE,INDUSTRY,MAIN_RPODUCT,SCOPE,CREDIT_LEVEL" +
                ",IS_TRADEFLAT_PROVIDER,STATUS,id,CREATE_USER_ID,CREATE_USER,CREATE_TIME," +
                "LAST_UPDATE_USER_ID,LAST_UPDATE_USER,LAST_UPDATE_TIME) " +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,(select sys_guid() from dual),?,?,?,?,?,?)";

        Object[] tObject = new Object[26] ;
          tObject[0]=aGSProviderBean.getGs_provider_id();
          tObject[1]=aGSProviderBean.getGs_provider_shortname();
          tObject[2]=aGSProviderBean.getGs_provider_name();
          tObject[3]=aGSProviderBean.getGs_provider_engname();
          tObject[4]=aGSProviderBean.getGs_corp_property();
          tObject[5]=aGSProviderBean.getGs_corp_addr();
          tObject[6]=aGSProviderBean.getGs_corp_post();
          tObject[7]=aGSProviderBean.getGs_principal_id();
          tObject[8]=aGSProviderBean.getGs_principal_phone();
          tObject[9]=aGSProviderBean.getGs_contact_id();
          tObject[10]=aGSProviderBean.getGs_contact_phone();
          tObject[11]=aGSProviderBean.getGs_reside_country();
          tObject[12]=aGSProviderBean.getGs_reside_province();
          tObject[13]=aGSProviderBean.getGs_reside_city();
          tObject[14]=aGSProviderBean.getGs_work_trade();
          tObject[15]=aGSProviderBean.getGs_work_manufacture();
          tObject[16]=aGSProviderBean.getGs_work_scope();
          tObject[17]=aGSProviderBean.getGs_credit_level();
          tObject[18]=aGSProviderBean.getGs_is_tradeflat_provider();
          tObject[19]=aGSProviderBean.getGs_validity()==null?"1":aGSProviderBean.getGs_validity();
          tObject[20] = aGSProviderBean.getCreate_user_id();
          tObject[21] = aGSProviderBean.getCreate_user();
          tObject[22] = aGSProviderBean.getCreate_time();
          tObject[23] = aGSProviderBean.getLast_update_user_id();
          tObject[24] = aGSProviderBean.getLast_update_user();
          tObject[25] = aGSProviderBean.getLast_update_time();
          
          
        boolean tResult = false;
        try {
            tResult = executeUpdate(con, tSql, tObject);

        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    
    public boolean addForDeliver(GSProviderBean aGSProviderBean) throws GSException {

        String tSql="INSERT INTO GS_PROVIDER (GS_PROVIDER_ID,GS_PROVIDER_SHORTNAME,GS_PROVIDER_NAME," +
                "GS_PROVIDER_ENGNAME,GS_CORP_PROPERTY,GS_CORP_ADDR,GS_CORP_POST,GS_PRINCIPAL_ID," +
                "GS_PRINCIPAL_PHONE,GS_CONTACT_ID,GS_CONTACT_PHONE,GS_RESIDE_COUNTRY,GS_RESIDE_PROVINCE," +
                "GS_RESIDE_CITY,GS_WORK_TRADE,GS_WORK_MANUFACTURE,GS_WORK_SCOPE,GS_CREDIT_LEVEL" +
                ",GS_IS_TRADEFLAT_PROVIDER) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        Object[] tObject = new Object[19] ;
          tObject[0]=aGSProviderBean.getGs_provider_id();
          tObject[1]=aGSProviderBean.getGs_provider_shortname();
          tObject[2]=aGSProviderBean.getGs_provider_name();
          tObject[3]=aGSProviderBean.getGs_provider_engname();
          tObject[4]=aGSProviderBean.getGs_corp_property();
          tObject[5]=aGSProviderBean.getGs_corp_addr();
          tObject[6]=aGSProviderBean.getGs_corp_post();
          tObject[7]=aGSProviderBean.getGs_principal_id();
          tObject[8]=aGSProviderBean.getGs_principal_phone();
          tObject[9]=aGSProviderBean.getGs_contact_id();
          tObject[10]=aGSProviderBean.getGs_contact_phone();
          tObject[11]=aGSProviderBean.getGs_reside_country();
          tObject[12]=aGSProviderBean.getGs_reside_province();
          tObject[13]=aGSProviderBean.getGs_reside_city();
          tObject[14]=aGSProviderBean.getGs_work_trade();
          tObject[15]=aGSProviderBean.getGs_work_manufacture();
          tObject[16]=aGSProviderBean.getGs_work_scope();
          tObject[17]=aGSProviderBean.getGs_credit_level();
          tObject[18]=aGSProviderBean.getGs_is_tradeflat_provider();
          
        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);

        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    
    /**
     * 供应商信息是否存在
     * @author owen
     * @param aProviderId
     * @return
     * @throws GSException
     */
    public boolean isExist(String aProviderId) throws GSException{
        String tSql = "select * from GS_PROVIDER where GS_PROVIDER_ID = ?";
        boolean tFlag = false;
        Object[] tObj = {aProviderId};
        try{
            ArrayList tList = this.executeQuery(new GSProviderBean(),tSql,tObj);
            if(tList.size() > 0){
                tFlag = true;
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tFlag;
    }
    /**
     * 查询所有供应商
     * @return
     * @throws GSException
     */
    public ArrayList queryAllProvider() throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = "select * from GS_PROVIDER order by GS_PROVIDER_ID";
        Object[] tObj = {};
        try{
            tList = this.executeQuery(new GSProviderBean(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tList;
    }
    
    /**
     * 在供应商表中删除一笔记录
     * 
     * @author owen
     * 
     * @param aProviderId 供应商编号
     *           
     */
    public boolean delete(GSProviderBean tGSProviderBean) throws GSException {
        String tSql = "update  t_PROVIDER set status='0' ,Last_update_user_id = ?" +
                ",Last_update_user = ?,Last_update_time = ? where code=?";
        Object[] tObject = {
                tGSProviderBean.getLast_update_user_id(),
                tGSProviderBean.getLast_update_user(),
                tGSProviderBean.getLast_update_time(),
                tGSProviderBean.getGs_provider_id()};
        boolean tResult = false;
        try {
            tResult=executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    /**
     * 在供应商表中设置供应商是否有效
     * 
     * @author owen
     * 
     * @param aProviderId 供应商编号
     *           
     */
    public boolean setIsOkOrNot(GSProviderBean tGSProviderBean,String type) throws GSException {
        String tSql = "update  t_PROVIDER set status= ? ,Last_update_user_id = ?" +
                ",Last_update_user = ?,Last_update_time = ? where code=?";
        Object[] tObject = {
                type,
                tGSProviderBean.getLast_update_user_id(),
                tGSProviderBean.getLast_update_user(),
                tGSProviderBean.getLast_update_time(),
                tGSProviderBean.getGs_provider_id()};
        boolean tResult = false;
        try {
            tResult=executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    
    
    /**
     * 在供应商表中更新一笔记录
     * 
     * @param MerchantTempBean
     * @throws CUPSException]
     *             return boolean
     */
    public boolean modify(GSProviderBean aGSProviderBean) throws GSException {
        
        String tSql="UPDATE t_PROVIDER SET NAME=?,FULL_NAME=?," +
                "ENG_NAME=?,COM_PROPERTY=?,ADDRESS=?,ZIP_CODE=?,MANAGE_PERSON=?," +
                "MANAGER_TEL=?,LINK_PERSON=?,LINK_TEL=?,COUNTRY_CODE=?,PROVINCE_CODE=?," +
                "DISTRICT_CODE=?,INDUSTRY=?,MAIN_RPODUCT=?,SCOPE=?,CREDIT_LEVEL=?,STATUS=? ,Last_update_user_id = ?" +
                ",Last_update_user = ?,Last_update_time = ? WHERE code=?";
        
        Object[] tObject = new Object[22];
        tObject[0] = aGSProviderBean.getGs_provider_shortname();
        tObject[1] = aGSProviderBean.getGs_provider_name();
        tObject[2] = aGSProviderBean.getGs_provider_engname();
        tObject[3] = aGSProviderBean.getGs_corp_property();
        tObject[4] = aGSProviderBean.getGs_corp_addr();
        tObject[5] = aGSProviderBean.getGs_corp_post();
        tObject[6] = aGSProviderBean.getGs_principal_id();
        tObject[7] = aGSProviderBean.getGs_principal_phone();
        
        tObject[8] = aGSProviderBean.getGs_contact_id();
        tObject[9] = aGSProviderBean.getGs_contact_phone();
        tObject[10] = aGSProviderBean.getGs_reside_country();
        tObject[11] = aGSProviderBean.getGs_reside_province();
        tObject[12] = aGSProviderBean.getGs_reside_city();
        tObject[13] = aGSProviderBean.getGs_work_trade();
        tObject[14] = aGSProviderBean.getGs_work_manufacture();
        tObject[15] = aGSProviderBean.getGs_work_scope();
        tObject[16] = aGSProviderBean.getGs_credit_level();
        tObject[17] = aGSProviderBean.getGs_validity()==null?"1":aGSProviderBean.getGs_validity();
        tObject[18] = aGSProviderBean.getLast_update_user_id();
        tObject[19] = aGSProviderBean.getLast_update_user();
        tObject[20] = aGSProviderBean.getLast_update_time();
        tObject[21] = aGSProviderBean.getGs_provider_id();
       

        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    
    public boolean modify(Connection conn, GSProviderBean aGSProviderBean) throws GSException {
        
        String tSql="UPDATE t_PROVIDER SET NAME=?,FULL_NAME=?," +
                "ENG_NAME=?,COM_PROPERTY=?,ADDRESS=?,ZIP_CODE=?,MANAGE_PERSON=?," +
                "MANAGER_TEL=?,LINK_PERSON=?,LINK_TEL=?,COUNTRY_CODE=?,PROVINCE_CODE=?," +
                "DISTRICT_CODE=?,INDUSTRY=?,MAIN_RPODUCT=?,SCOPE=?,CREDIT_LEVEL=?,STATUS=? ,Last_update_user_id = ?" +
                ",Last_update_user = ?,Last_update_time = ? WHERE code=?";
        
        Object[] tObject = new Object[22];
        tObject[0] = aGSProviderBean.getGs_provider_shortname();
        tObject[1] = aGSProviderBean.getGs_provider_name();
        tObject[2] = aGSProviderBean.getGs_provider_engname();
        tObject[3] = aGSProviderBean.getGs_corp_property();
        tObject[4] = aGSProviderBean.getGs_corp_addr();
        tObject[5] = aGSProviderBean.getGs_corp_post();
        tObject[6] = aGSProviderBean.getGs_principal_id();
        tObject[7] = aGSProviderBean.getGs_principal_phone();
        
        tObject[8] = aGSProviderBean.getGs_contact_id();
        tObject[9] = aGSProviderBean.getGs_contact_phone();
        tObject[10] = aGSProviderBean.getGs_reside_country();
        tObject[11] = aGSProviderBean.getGs_reside_province();
        tObject[12] = aGSProviderBean.getGs_reside_city();
        tObject[13] = aGSProviderBean.getGs_work_trade();
        tObject[14] = aGSProviderBean.getGs_work_manufacture();
        tObject[15] = aGSProviderBean.getGs_work_scope();
        tObject[16] = aGSProviderBean.getGs_credit_level();
        tObject[17] = aGSProviderBean.getGs_validity()==null?"1":aGSProviderBean.getGs_validity();
        tObject[18] = aGSProviderBean.getLast_update_user_id();
        tObject[19] = aGSProviderBean.getLast_update_user();
        tObject[20] = aGSProviderBean.getLast_update_time();
        tObject[21] = aGSProviderBean.getGs_provider_id();
       

        boolean tResult = false;
        try {
            tResult = executeUpdate(conn, tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    
    /**
     * 根据集中编码和是否交易平台特约产品随机查询一个供应商
     * @param concentricityId
     * @param isTrade
     * @return
     * @throws GSException
     */
    public ArrayList queryManufactureProvider(Connection conn, String concentricityId) throws GSException {
        String tSql = "select c.* from GS_MANUFACTURE b,GS_PROVIDER c where b.GS_PROVIDER_ID = c.GS_PROVIDER_ID and b.GS_CONCENTRICITY_ID=? and b.GS_IS_TRADEFLAT_MANUFACTURE=?";
        ArrayList tList = new ArrayList();

        Object[] tObj = null;
        try{
            tObj = new Object[]{concentricityId,Integer.toString(0)};
            tList = this.executeQuery(conn,new GSProviderBean(),tSql,tObj);
            if(tList.size() == 0){
                tObj = new Object[]{concentricityId,Integer.toString(1)};
                tList = this.executeQuery(conn,new GSProviderBean(),tSql,tObj);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
        
    }
    /**
     * 根据是否交易平台供应商标志查询供应商
     * @param aIsFlat    是否交易平台供应商
     * @return      
     * @throws GSException
     */
    public ArrayList queryAllProviderByFlat(String aIsFlat) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = "select * from GS_PROVIDER where GS_IS_TRADEFLAT_PROVIDER = ?";
        Object[] tObj = {aIsFlat};
        try{
            tList = this.executeQuery(new GSProviderBean(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 根据集中编码查询供应商及产品信息
     * @param conn                  DB连接
     * @param concentricityId       集中编码
     * @return
     * @throws GSException
     */
    public ArrayList queryProvider(Connection conn, String concentricityId) throws GSException {
        String tSql = "select c.CODE as P1,c.NAME as P2,b.APEP_CODE as P3," +
                "b.UNIT_PRICE as P4,c.IS_TRADEFLAT_PROVIDER as P5,b.IS_TRADEFLAT_PRODUCT as P6,b.BALANCE_MODE as P7,c.STATUS as P8 " +
                "from t_product b,t_PROVIDER c where b.PROVIDER_CODE = c.CODE and b.CONCENTRICITY_CODE=? and b.STATUS='1'";
        ArrayList tList = new ArrayList();
        Object[] tObj = {concentricityId};
        try{
            tList = this.executeQuery(conn,new GSProviderVO(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
        
    }
    /**
     * 根据名称查询供应商信息
     * @param aName             名称
     * @return
     * @throws GSException
     */
    public GSProviderBean queryProviderByName(String aName) throws GSException{
        GSProviderBean tProvBean = null;
        String tSql = "select * from T_PROVIDER where NAME='" + aName +"' or FULL_NAME='" + aName + "'";
        ArrayList tList = new ArrayList();
        Object[] tObj = null;
        try{
            tList = this.executeQuery(new GSProviderBean(),tSql,tObj);
            if(tList.size() > 0){
                tProvBean = (GSProviderBean)tList.get(0);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tProvBean;
    }
    /**
     * 根据供应商简称查询供应商信息
     * @param aName         简称
     * @return
     * @throws GSException
     */
    public GSProviderBean queryProviderByShortName(String aName) throws GSException{
        GSProviderBean tProvBean = null;
        String tSql = "select * from GS_PROVIDER where GS_PROVIDER_SHORTNAME='" + aName + "'";
        ArrayList tList = new ArrayList();
        Object[] tObj = null;
        try{
            tList = this.executeQuery(new GSProviderBean(),tSql,tObj);
            if(tList.size() == 1){
                tProvBean = (GSProviderBean)tList.get(0);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tProvBean;
    }
    /**
     * 根据产品编码查询其供应商信息
     * @param aManufId          产品编码
     * @return
     * @throws GSException
     */
    public GSProviderBean queryProviderNameByManuf(String aManufId) throws GSException{
        GSProviderBean tProviderBean = null;
        StringBuffer tSql = new StringBuffer("select A.* from GS_PROVIDER A,GS_MANUFACTURE B ");
        tSql.append("where A.GS_PROVIDER_ID=B.GS_PROVIDER_ID and B.GS_MANUFACTURE_ID='" + aManufId + "'");
        try{
            ArrayList tList = this.executeQuery(new GSProviderBean(),tSql.toString(),null);
            if(tList.size() == 1){
                tProviderBean = (GSProviderBean)tList.get(0);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tProviderBean;
    }
    
    /** 占用最大编号  */
    public boolean engrossMaxCode(String code)throws GSException{
        String sql = "insert into t_provider "+
        "id,code,name,full_name,com_property,country_code,province_code,district_code,is_tradeflat_provider,"+
        "status,create_user_id,create_user,create_time,last_update_user_id,last_update_user,last_update_time "+
        "values((select sys_guid() from dual),'"+code+"','1','1','1','1','1','1','1','1','1','1','1','1','1','1')";
        try {
            return executeUpdate(sql,null);
        } catch (SQLException e) {
            GSLogWriter.error(this, e.toString());
            throw new GSException(e.getMessage());
        }
    }
    
    /**
     * 根据供应商简称或编码查询供应商信息
     * 
     * @param aName
     *            简称或id
     * @return
     * @throws GSException
     */
    public GSProviderBean queryProviderByShortNameOrId(String aName) throws GSException {
        GSProviderBean tProvBean = null;
        String tSql = "select * from T_PROVIDER where name=? or code=?";
        ArrayList tList = new ArrayList();
        Object[] obj={aName,aName};
        try {
            tList = this.executeQuery(new GSProviderBean(), tSql, obj);
            if (tList.size() == 1) {
                tProvBean = (GSProviderBean) tList.get(0);
            }
        } catch (Exception e) {
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tProvBean;
    }
    
    /**
     * 根据供应商编码查询是否是审核类
     */
    public boolean isAuditProvider(String aProviderCode) throws GSException {
		String tSql = "select * from t_provider_type where provider_code = ? and status = '1'";
		Connection con = null;
		java.sql.PreparedStatement prp = null;
		
		try {
			DBConnectionManager tDBConn = DBConnectionManager.getInstance();
			con = tDBConn.getConnection();
			prp = con.prepareStatement(tSql);
			prp.setString(1, aProviderCode);
			if(prp.executeQuery().next()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		} finally {
			try {
				if(prp != null) {
					prp.close();
				}
				if(con != null) {
					con.close();
				}
			} catch (SQLException e) {
				GSLogWriter.error(e.getLocalizedMessage(), e);
			}
		}
    }
    
    
    private final static String INSERT_PROVIDER_AUDIT_SQL = 
    		"insert into t_provider_type (ID, PROVIDER_ID, PROVIDER_CODE, PROVIDER_TYPE, REMARK, CREATE_TIME, CREATE_USER, CREATE_USER_ID, LAST_UPDATE_TIME, LAST_UPDATE_USER, LAST_UPDATE_USER_ID,STATUS) " +
    		"values (sys_guid(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
    /**
     * 插入审核类供应商记录
     * @param con
     * @param aProvider
     * @param loginContext
     * @return
     * @throws Exception
     */
    public boolean insertProvierAuditType(Connection con, GSProviderBean aProvider, LoginContext loginContext) throws Exception {
    	try {
    		if(con != null) {
    			
    			String tCurrentTime = new DateUtil().getFullString();
    			GSProviderBean tProvider = queryProvider(aProvider.getGs_provider_id());
    			Object[] tStmntParams = new Object[]{
    					tProvider.getId(),
    					tProvider.getGs_provider_id(),
    					new Integer(GSBaseConstants.PROVIDER_TYPE_AUDIT),
    					"",
    					tCurrentTime,
    					loginContext.getLoginUserName(),
    					loginContext.getLoginUserId(),
    					tCurrentTime,
    					loginContext.getLoginUserName(),
    					loginContext.getLoginUserId(),
    					ConstDefine.STR_VALID_YES
    			};
    			return this.executeUpdate(con, INSERT_PROVIDER_AUDIT_SQL, tStmntParams);
    		} else {
    			throw new GSException("Connection is null");
    		}
    	} catch(Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
    	} 
    }
    
    private final static String DEL_PROVIDER_AUDIT_SQL = 
		"delete from t_provider_type where provider_code = ?"; 
    private final static String UPDATE_STATUS_PROVIDER_AUDIT_SQL = 
    	" update t_provider_type set status = ? where provider_code = ?";
    /**
     * 删除t_provider_type表中指定供应商编码的数据,逻辑删除
     */
    public boolean deleteProviderAuditType(Connection con, String providerCode) throws Exception {
    	try {
    		if(con != null) {
    			
    			String tCurrentTime = new DateUtil().getFullString();
    			
    			Object[] tStmntParams = new Object[]{
    					ConstDefine.STR_VALID_NO,
    					providerCode
    			};
    			return this.executeUpdate(con, UPDATE_STATUS_PROVIDER_AUDIT_SQL, tStmntParams);
    		} else {
    			throw new GSException("Connection is null");
    		}
    	} catch(Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
    	} 
    }
}
